"""
    本文件用于：case详情页的所有SQL语句
"""


def construct_sql_case_statistics_detail_old(
        _tag_type, _case_type, _subsystem, _milestone_version, _verison_NO, _tester_name, _date_range
,_ids=None):
    # print('+++++++++++++++++++++++++++', _tag_type, _case_type, _subsystem, _milestone_version, _verison_NO, _tester_name, _date_range, _ids)
    return f"""
SELECT
                cas.id as case_id, cas.title as title, cas.`status` as `status`, cas.pri as priority, usr.realname as creator, cas.lastRunResult as result, cas.openedDate as create_date,
                task.`name` as task_name, task.id as task_id, 
								pject.milestone, pject.id as pject_id, pject.`name` as pject_name,
								prod.`name` as prod_name, prod.id as prod_id
FROM
						zentao.zt_case as cas
LEFT JOIN
						zentao.zt_user as usr
ON
						usr.account = cas.openedBy
LEFT JOIN
						zentao.zt_product as prod
ON
						prod.id=cas.product
LEFT JOIN
						zentao.zt_testtask as task
ON
						task.product = prod.id
LEFT JOIN
						zentao.zt_project as pject
ON
						pject.id = prod.program
WHERE
						cas.deleted = '0'
{
'''AND
		usr.realname = '%s' # 姓名''' % _tester_name if _tester_name else ''
}
{
'''AND
		cas.product = %d # 子系统''' % _subsystem if type(_subsystem) is int else ''
}
{
'''AND
		pject.milestone = '%s' # 版本类型''' % _milestone_version if _milestone_version else ''
}
{
'''AND
		task.`name` = '%s' # 版本号''' % _verison_NO if _verison_NO else ''
}
{
'''AND
		cas.openedDate > DATE_SUB(CURRENT_DATE, INTERVAL %d day) # 时间范围
AND
		cas.openedDate <= CURRENT_DATE'''  % _date_range if type(_date_range) is int else ''
}
{
'''AND
		cas.`status` = '%s' # case状态标签''' % _case_type if _case_type else ''
}
{
'''AND
		cas.`lastRunResult` = '%s' # case结果''' % _tag_type if _tag_type else ''
}
{
'''AND
		cas.id = '%d' # id
	group by 
	    cas.id''' % _ids if _ids and len(_ids) == 1 else (
    ''  if not _ids 
        else 
f'''AND
		cas.id in {_ids} # id
    group by 
	    cas.id		
		''')
}
limit 20
"""


def construct_sql_case_statistics_detail(
        _project_id,
        _subsys_id,
        _test_sheet_id,
        _creator_name,
        _executor_name,
        _execute_result,
        _date_execute_start,
        _date_execute_end,
        _case_status,
        _case_type,
        _pageNo=0,
        _pageNum=25):
    return f"""
# 用例统计详情
select 
            pject.id as project_id, 
            pj.id as subsys_id, 
            pject.name as pject_name,
            pj.name as subsys_name,
            tsk.id as task_id,
            tsk.name as task_name,
            cas.id as case_id,
            cas.pri as priority,
            cas.title as title,
            cas.status as status,
            cas.type as case_type,
            usr.realname as creator,
            usrB.realname as executor,
            cas.openedDate as create_date,
            trun.lastRunDate as execute_date,
            trun.lastRunResult as last_run_result,
            count(1) as execute_num
			
FROM
			zt_testresult as trest 
LEFT JOIN
			zt_testrun as trun 
ON
			trun.id = trest.run
LEFT JOIN
			zt_testtask as tsk 
on 
			tsk.id=trun.task 
LEFT JOIN
			zt_case as cas 
ON
			cas.id=trun.`case`
LEFT JOIN
			zt_project as pj 
ON
			pj.id=tsk.project
LEFT JOIN
			zt_project as pject 
ON
			SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
LEFT JOIN
			zt_user as usr 
ON
			usr.account=cas.openedBy
LEFT JOIN
			zt_user as usrB 
ON
			usrB.account=trun.lastRunner
WHERE
			trest.run > 0
AND
			cas.deleted = '0'
AND
			pj.deleted = '0'
AND
			tsk.deleted = '0'	
{
'''AND
		usr.realname = '%s' # 创建人''' % _creator_name if _creator_name else ''
}
{
'''AND
		pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else 
        '''AND
            pj.id = %d # 项目id''' if _project_id == 0
        else ''
}
{
'''AND
		pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
}
{
'''AND
		tsk.id = %d # 测试单id''' % _test_sheet_id if type(_test_sheet_id) is int and _test_sheet_id > 0 else ''
}
{
'''AND
		usrB.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
}
{
'''AND
		trun.lastRunResult = '%s' # 用例执行结果''' % _execute_result if _execute_result else ''
}
{
'''AND
		cas.status = '%s' # 用例状态''' % _case_status if _case_status else ''
}
{
'''AND
		cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
}
{
f'''
		{"AND trun.lastRunDate >= '%s' # 创建的时间" % _date_execute_start if _date_execute_start else ''} 
		{"AND trun.lastRunDate <= '%s' # 创建的时间" % _date_execute_end if _date_execute_end else ''}'''
}
GROUP BY
			trest.case, trest.run, trest.lastRunner
order by 
                trun.lastRunDate
desc
limit {_pageNo * _pageNum}, {_pageNum}
"""


# 统计所有的用例执行情况
def construct_sql_case_statistics_all_sql_detail(
        _project_id,
        _subsys_id,
        _test_sheet_id,
        _creator_name,
        _executor_name,
        _execute_result,
        _date_execute_start,
        _date_execute_end,
        _case_status,
        _case_type
):
    return f"""
# 执行统计聚合结果
select 
            count(id) as total
from
    (select 	
                trest.id as id 
    FROM
                zt_testresult as trest 
    LEFT JOIN
                zt_testrun as trun 
    ON
                trun.id = trest.run
    LEFT JOIN
                zt_testtask as tsk 
    on 
                tsk.id=trun.task 
    LEFT JOIN
                zt_case as cas 
    ON
                cas.id=trun.`case`
    LEFT JOIN
                zt_project as pj 
    ON
                pj.id=tsk.project
    LEFT JOIN
                zt_project as pject 
    ON
                SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    LEFT JOIN
                zt_user as usr 
    ON
                usr.account=cas.openedBy
    LEFT JOIN
                zt_user as usrB 
    ON
                usrB.account=trun.lastRunner
    WHERE
                trest.run > 0
    AND
                cas.deleted = '0'
    AND
                pj.deleted = '0'
    AND
                tsk.deleted = '0'	
    {
        '''AND
                usr.realname = '%s' # 创建人''' % _creator_name if _creator_name else ''
        }
    {
        '''AND
                pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else 
            '''AND
                bug.project = %d # 项目id''' % _project_id if _project_id == 0
        else ''
        }
    {
        '''AND
                pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
        }
    {
        '''AND
                tsk.id = %d # 测试单id''' % _test_sheet_id if type(_test_sheet_id) is int and _test_sheet_id > 0 else ''
        }
    {
        '''AND
                usrB.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
        }
    {
        '''AND
                trun.lastRunResult = '%s' # 用例执行结果''' % _execute_result if _execute_result else ''
        }
    {
        '''AND
                cas.status = '%s' # 用例状态''' % _case_status if _case_status else ''
        }
    {
        '''AND
                cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
        }
    {
        f'''
            {"AND trun.lastRunDate >= '%s' # 创建的时间" % _date_execute_start if _date_execute_start else ''} 
            {"AND trun.lastRunDate <= '%s' # 创建的时间" % _date_execute_end if _date_execute_end else ''}'''
        }
    GROUP BY
                trest.case, trest.run, trest.lastRunner
    ) as new
"""


# 执行用例总数
def construct_sql_execute_case_statistics_all(
        _project_id,
        _subsys_id,
        _test_sheet_id,
        _executor_name,
        _date_execute_start,
        _date_execute_end,
        _case_status,
        _case_type
):
    return f"""
# 执行用例总数
select 
            count(id) as total
from
    (select 	
                trest.id as id 
    FROM
                zt_testresult as trest 
    LEFT JOIN
                zt_testrun as trun 
    ON
                trun.id = trest.run
    LEFT JOIN
                zt_testtask as tsk 
    on 
                tsk.id=trun.task 
    LEFT JOIN
                zt_case as cas 
    ON
                cas.id=trun.`case`
    LEFT JOIN
                zt_project as pj 
    ON
                pj.id=tsk.project
    LEFT JOIN
                zt_project as pject 
    ON
                SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    LEFT JOIN
                zt_user as usr 
    ON
                usr.account=cas.openedBy
    LEFT JOIN
                zt_user as usrB 
    ON
                usrB.account=trun.lastRunner
    WHERE
                trest.run > 0
    AND
                cas.deleted = '0'
    AND
                pj.deleted = '0'
    AND
                tsk.deleted = '0'	
    {
        '''AND
                pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else 
         '''AND
                bug.project = %d # 项目id''' % _project_id if _project_id == 0
        else ''
        }
    {
        '''AND
                pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
        }
    {
        '''AND
                tsk.id = %d # 测试单id''' % _test_sheet_id if type(_test_sheet_id) is int and _test_sheet_id > 0 else ''
        }
    {
        '''AND
                usrB.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
        }
    {
        '''AND
                cas.status = '%s' # 用例状态''' % _case_status if _case_status else ''
        }
    {
        '''AND
                cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
        }
    {
        f'''
            {"AND trun.lastRunDate >= '%s'" % _date_execute_start if _date_execute_start else ''} # 创建的时间
            {"AND trun.lastRunDate <= '%s'" % _date_execute_end if _date_execute_end else ''}'''
        }
    
     group by trest.case
    
    ) as new

"""


# 执行次数总数
def construct_sql_execute_times_statistics_all(
        _project_id,
        _subsys_id,
        _test_sheet_id,
        _executor_name,
        _date_execute_start,
        _date_execute_end,
        _case_status,
        _case_type
):
    return f"""
# 执行用例总数
select 
            count(id) as total
from
    (select 	
                trest.id as id 
    FROM
                zt_testresult as trest 
    LEFT JOIN
                zt_testrun as trun 
    ON
                trun.id = trest.run
    LEFT JOIN
                zt_testtask as tsk 
    on 
                tsk.id=trun.task 
    LEFT JOIN
                zt_case as cas 
    ON
                cas.id=trun.`case`
    LEFT JOIN
                zt_project as pj 
    ON
                pj.id=tsk.project
    LEFT JOIN
                zt_project as pject 
    ON
                SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    LEFT JOIN
                zt_user as usr 
    ON
                usr.account=cas.openedBy
    LEFT JOIN
                zt_user as usrB 
    ON
                usrB.account=trun.lastRunner
    WHERE
                trest.run > 0
    AND
                cas.deleted = '0'
    AND
                pj.deleted = '0'
    AND
                tsk.deleted = '0'	
    {
        '''AND
                pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id >= 0 else ''
        }
    {
        '''AND
                pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
        }
    {
        '''AND
                tsk.id = %d # 测试单id''' % _test_sheet_id if type(_test_sheet_id) is int and _test_sheet_id > 0 else ''
        }
    {
        '''AND
                usrB.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
        }
    {
        '''AND
                cas.status = '%s' # 用例状态''' % _case_status if _case_status else ''
        }
    {
        '''AND
                cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
        }
    {
        f'''
            {"AND trun.lastRunDate > '%s'" % _date_execute_start if _date_execute_start else ''} # 创建的时间
            {"AND trun.lastRunDate <= '%s'" % _date_execute_end if _date_execute_end else ''}'''
        }
    ) as new

"""







case_tester_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_case as cas
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=cas.openedBy
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		cas.openedBy
"""


def construct_sql_case_version_by_subsys_search_sql(_subsys_id=None):
    return  f"""
select 
        id, name 
from
    zt_project as proj
where
    proj.type='sprint'
and 
    proj.deleted = '0'
{
    f'and proj.parent={_subsys_id}' if _subsys_id 
    else ''
}
;"""

# 查询所有的子系统
def construct_sql_case_subsys_by_project_search_sql(_project_id=None):
    return f"""
select 
        proj.id as id, proj.name as name
from
        zt_project as proj   
where
        proj.type='project'
and
        proj.parent > 0
and
        proj.deleted = '0'
{
    f'and proj.parent={_project_id}' if _project_id 
    else ''
}
"""


# 查询所有的测试单
def construct_sql_case_test_sheet_by_project_search_sql(
        _project_id=None,
        _subsys_id=None
):
    return f"""
# 查詢所有的测试单
	SELECT
			tsk.id as task_id,
			tsk.`name` as task_name
	FROM
			zt_testtask as tsk 
	LEFT JOIN
			zt_project as pj # 子系统
	ON
			pj.id = tsk.project
	LEFT JOIN
			zt_project as pject # 项目
	ON
			pject.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path, ',', 2), ',', -1)
	WHERE
			tsk.deleted='0'
{
    f'and pject.id={_project_id}' if _project_id 
    else ''
}
{
    f'and pj.id={_subsys_id}' if _subsys_id 
    else ''
}
"""



# 查询所有的项目
case_all_project_name_list_sql = """
select 
		id, `name`, `status`
FROM
		zt_project
WHERE
		type in ('program', 'project')
AND
		project=0
AND
        parent=0
AND
		deleted='0'

;"""


case_executor_name_list_sql = """
select 
		usr.realname as name
FROM
		zt_case as cas 
LEFT JOIN
		zt_user as usr 
ON
		usr.account=cas.lastRunner
WHERE
		cas.lastRunner is not NULL
AND
		usr.realname != 'admin'
group by 
		cas.lastRunner 

;"""


def construct_sql_case_execute_data_distribute_by_date(
    _date_execute_start,
    _date_execute_end
):
    return f"""
(
select
    "【总数】" as executor,
    count(if(Day(trun.lastRunDate) >= 1  and Day(trun.lastRunDate) < 2, 1 , null)) as "1",
    count(if(Day(trun.lastRunDate) >= 2  and Day(trun.lastRunDate) < 3, 1 , null)) as "2",
    count(if(Day(trun.lastRunDate) >= 3  and Day(trun.lastRunDate) < 4, 1 , null)) as "3",
    count(if(Day(trun.lastRunDate) >= 4  and Day(trun.lastRunDate) < 5, 1 , null)) as "4",
    count(if(Day(trun.lastRunDate) >= 5  and Day(trun.lastRunDate) < 6, 1 , null)) as "5",
    count(if(Day(trun.lastRunDate) >= 6  and Day(trun.lastRunDate) < 7, 1 , null)) as "6",
    count(if(Day(trun.lastRunDate) >= 7  and Day(trun.lastRunDate) < 8, 1 , null)) as "7",
    count(if(Day(trun.lastRunDate) >= 8  and Day(trun.lastRunDate) < 9, 1 , null)) as "8",
    count(if(Day(trun.lastRunDate) >= 9  and Day(trun.lastRunDate) < 10, 1 , null)) as "9",
    count(if(Day(trun.lastRunDate) >= 10 and Day(trun.lastRunDate) < 11, 1 , null)) as "10",
    count(if(Day(trun.lastRunDate) >= 11 and Day(trun.lastRunDate) < 12, 1 , null)) as "11",
    count(if(Day(trun.lastRunDate) >= 12 and Day(trun.lastRunDate) < 13, 1 , null)) as "12",
    count(if(Day(trun.lastRunDate) >= 13 and Day(trun.lastRunDate) < 14, 1 , null)) as "13",
    count(if(Day(trun.lastRunDate) >= 14 and Day(trun.lastRunDate) < 15, 1 , null)) as "14",
    count(if(Day(trun.lastRunDate) >= 15 and Day(trun.lastRunDate) < 16, 1 , null)) as "15",
    count(if(Day(trun.lastRunDate) >= 16 and Day(trun.lastRunDate) < 17, 1 , null)) as "16",
    count(if(Day(trun.lastRunDate) >= 17 and Day(trun.lastRunDate) < 18, 1 , null)) as "17",
    count(if(Day(trun.lastRunDate) >= 18 and Day(trun.lastRunDate) < 19, 1 , null)) as "18",
    count(if(Day(trun.lastRunDate) >= 19 and Day(trun.lastRunDate) < 20, 1 , null)) as "19",
    count(if(Day(trun.lastRunDate) >= 20 and Day(trun.lastRunDate) < 21, 1 , null)) as "20",
    count(if(Day(trun.lastRunDate) >= 21 and Day(trun.lastRunDate) < 22, 1 , null)) as "21",
    count(if(Day(trun.lastRunDate) >= 22 and Day(trun.lastRunDate) < 23, 1 , null)) as "22",
    count(if(Day(trun.lastRunDate) >= 23 and Day(trun.lastRunDate) < 24, 1 , null)) as "23",
    count(if(Day(trun.lastRunDate) >= 24 and Day(trun.lastRunDate) < 25, 1 , null)) as "24",
    count(if(Day(trun.lastRunDate) >= 25 and Day(trun.lastRunDate) < 26, 1 , null)) as "25",
    count(if(Day(trun.lastRunDate) >= 26 and Day(trun.lastRunDate) < 27, 1 , null)) as "26",
    count(if(Day(trun.lastRunDate) >= 27 and Day(trun.lastRunDate) < 28, 1 , null)) as "27",
    count(if(Day(trun.lastRunDate) >= 28 and Day(trun.lastRunDate) < 29, 1 , null)) as "28",
    count(if(Day(trun.lastRunDate) >= 29 and Day(trun.lastRunDate) < 30, 1 , null)) as "29",
    count(if(Day(trun.lastRunDate) >= 30 and Day(trun.lastRunDate) < 31, 1 , null)) as "30",
    count(if(Day(trun.lastRunDate) = 31, 1 , null)) as "31",
    count(1) as total

from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
)

UNION

(
select
    usr.realname as executor,
    count(if(Day(trun.lastRunDate) >= 1  and Day(trun.lastRunDate) < 2, 1 , null)) as "1",
    count(if(Day(trun.lastRunDate) >= 2  and Day(trun.lastRunDate) < 3, 1 , null)) as "2",
    count(if(Day(trun.lastRunDate) >= 3  and Day(trun.lastRunDate) < 4, 1 , null)) as "3",
    count(if(Day(trun.lastRunDate) >= 4  and Day(trun.lastRunDate) < 5, 1 , null)) as "4",
    count(if(Day(trun.lastRunDate) >= 5  and Day(trun.lastRunDate) < 6, 1 , null)) as "5",
    count(if(Day(trun.lastRunDate) >= 6  and Day(trun.lastRunDate) < 7, 1 , null)) as "6",
    count(if(Day(trun.lastRunDate) >= 7  and Day(trun.lastRunDate) < 8, 1 , null)) as "7",
    count(if(Day(trun.lastRunDate) >= 8  and Day(trun.lastRunDate) < 9, 1 , null)) as "8",
    count(if(Day(trun.lastRunDate) >= 9  and Day(trun.lastRunDate) < 10, 1 , null)) as "9",
    count(if(Day(trun.lastRunDate) >= 10 and Day(trun.lastRunDate) < 11, 1 , null)) as "10",
    count(if(Day(trun.lastRunDate) >= 11 and Day(trun.lastRunDate) < 12, 1 , null)) as "11",
    count(if(Day(trun.lastRunDate) >= 12 and Day(trun.lastRunDate) < 13, 1 , null)) as "12",
    count(if(Day(trun.lastRunDate) >= 13 and Day(trun.lastRunDate) < 14, 1 , null)) as "13",
    count(if(Day(trun.lastRunDate) >= 14 and Day(trun.lastRunDate) < 15, 1 , null)) as "14",
    count(if(Day(trun.lastRunDate) >= 15 and Day(trun.lastRunDate) < 16, 1 , null)) as "15",
    count(if(Day(trun.lastRunDate) >= 16 and Day(trun.lastRunDate) < 17, 1 , null)) as "16",
    count(if(Day(trun.lastRunDate) >= 17 and Day(trun.lastRunDate) < 18, 1 , null)) as "17",
    count(if(Day(trun.lastRunDate) >= 18 and Day(trun.lastRunDate) < 19, 1 , null)) as "18",
    count(if(Day(trun.lastRunDate) >= 19 and Day(trun.lastRunDate) < 20, 1 , null)) as "19",
    count(if(Day(trun.lastRunDate) >= 20 and Day(trun.lastRunDate) < 21, 1 , null)) as "20",
    count(if(Day(trun.lastRunDate) >= 21 and Day(trun.lastRunDate) < 22, 1 , null)) as "21",
    count(if(Day(trun.lastRunDate) >= 22 and Day(trun.lastRunDate) < 23, 1 , null)) as "22",
    count(if(Day(trun.lastRunDate) >= 23 and Day(trun.lastRunDate) < 24, 1 , null)) as "23",
    count(if(Day(trun.lastRunDate) >= 24 and Day(trun.lastRunDate) < 25, 1 , null)) as "24",
    count(if(Day(trun.lastRunDate) >= 25 and Day(trun.lastRunDate) < 26, 1 , null)) as "25",
    count(if(Day(trun.lastRunDate) >= 26 and Day(trun.lastRunDate) < 27, 1 , null)) as "26",
    count(if(Day(trun.lastRunDate) >= 27 and Day(trun.lastRunDate) < 28, 1 , null)) as "27",
    count(if(Day(trun.lastRunDate) >= 28 and Day(trun.lastRunDate) < 29, 1 , null)) as "28",
    count(if(Day(trun.lastRunDate) >= 29 and Day(trun.lastRunDate) < 30, 1 , null)) as "29",
    count(if(Day(trun.lastRunDate) >= 30 and Day(trun.lastRunDate) < 31, 1 , null)) as "30",
    count(if(Day(trun.lastRunDate) = 31, 1 , null)) as "31",
    count(1) as total

from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
and
    pject.status != 'closed'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
group by
    trun.lastRunner
order by
        total
    desc
)
"""


def construct_sql_case_execute_data_distribute_by_date_with_all(
    _date_execute_start,
    _date_execute_end,
    _executor_name,
    _case_type
):
    return f"""
select
    "【总数】" as executor,
    count(if(Day(trun.lastRunDate) >= 1  and Day(trun.lastRunDate) < 2, 1 , null)) as "1",
    count(if(Day(trun.lastRunDate) >= 2  and Day(trun.lastRunDate) < 3, 1 , null)) as "2",
    count(if(Day(trun.lastRunDate) >= 3  and Day(trun.lastRunDate) < 4, 1 , null)) as "3",
    count(if(Day(trun.lastRunDate) >= 4  and Day(trun.lastRunDate) < 5, 1 , null)) as "4",
    count(if(Day(trun.lastRunDate) >= 5  and Day(trun.lastRunDate) < 6, 1 , null)) as "5",
    count(if(Day(trun.lastRunDate) >= 6  and Day(trun.lastRunDate) < 7, 1 , null)) as "6",
    count(if(Day(trun.lastRunDate) >= 7  and Day(trun.lastRunDate) < 8, 1 , null)) as "7",
    count(if(Day(trun.lastRunDate) >= 8  and Day(trun.lastRunDate) < 9, 1 , null)) as "8",
    count(if(Day(trun.lastRunDate) >= 9  and Day(trun.lastRunDate) < 10, 1 , null)) as "9",
    count(if(Day(trun.lastRunDate) >= 10 and Day(trun.lastRunDate) < 11, 1 , null)) as "10",
    count(if(Day(trun.lastRunDate) >= 11 and Day(trun.lastRunDate) < 12, 1 , null)) as "11",
    count(if(Day(trun.lastRunDate) >= 12 and Day(trun.lastRunDate) < 13, 1 , null)) as "12",
    count(if(Day(trun.lastRunDate) >= 13 and Day(trun.lastRunDate) < 14, 1 , null)) as "13",
    count(if(Day(trun.lastRunDate) >= 14 and Day(trun.lastRunDate) < 15, 1 , null)) as "14",
    count(if(Day(trun.lastRunDate) >= 15 and Day(trun.lastRunDate) < 16, 1 , null)) as "15",
    count(if(Day(trun.lastRunDate) >= 16 and Day(trun.lastRunDate) < 17, 1 , null)) as "16",
    count(if(Day(trun.lastRunDate) >= 17 and Day(trun.lastRunDate) < 18, 1 , null)) as "17",
    count(if(Day(trun.lastRunDate) >= 18 and Day(trun.lastRunDate) < 19, 1 , null)) as "18",
    count(if(Day(trun.lastRunDate) >= 19 and Day(trun.lastRunDate) < 20, 1 , null)) as "19",
    count(if(Day(trun.lastRunDate) >= 20 and Day(trun.lastRunDate) < 21, 1 , null)) as "20",
    count(if(Day(trun.lastRunDate) >= 21 and Day(trun.lastRunDate) < 22, 1 , null)) as "21",
    count(if(Day(trun.lastRunDate) >= 22 and Day(trun.lastRunDate) < 23, 1 , null)) as "22",
    count(if(Day(trun.lastRunDate) >= 23 and Day(trun.lastRunDate) < 24, 1 , null)) as "23",
    count(if(Day(trun.lastRunDate) >= 24 and Day(trun.lastRunDate) < 25, 1 , null)) as "24",
    count(if(Day(trun.lastRunDate) >= 25 and Day(trun.lastRunDate) < 26, 1 , null)) as "25",
    count(if(Day(trun.lastRunDate) >= 26 and Day(trun.lastRunDate) < 27, 1 , null)) as "26",
    count(if(Day(trun.lastRunDate) >= 27 and Day(trun.lastRunDate) < 28, 1 , null)) as "27",
    count(if(Day(trun.lastRunDate) >= 28 and Day(trun.lastRunDate) < 29, 1 , null)) as "28",
    count(if(Day(trun.lastRunDate) >= 29 and Day(trun.lastRunDate) < 30, 1 , null)) as "29",
    count(if(Day(trun.lastRunDate) >= 30 and Day(trun.lastRunDate) < 31, 1 , null)) as "30",
    count(if(Day(trun.lastRunDate) = 31, 1 , null)) as "31",
    count(1) as total

from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
LEFT JOIN
    zt_testtask as tsk 
on 
    tsk.id=trun.task 
LEFT JOIN
    zt_project as pj 
ON
    pj.id=tsk.project
LEFT JOIN
    zt_project as pject 
ON
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
# and
#     pject.status != "closed"
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
"""


def construct_sql_case_execute_data_distribute_by_date_with_each(
    _date_execute_start,
    _date_execute_end,
    _executor_name,
    _case_type
):
    return f"""
select
    usr.realname as executor,
    count(if(Day(trun.lastRunDate) >= 1  and Day(trun.lastRunDate) < 2, 1 , null)) as "1",
    count(if(Day(trun.lastRunDate) >= 2  and Day(trun.lastRunDate) < 3, 1 , null)) as "2",
    count(if(Day(trun.lastRunDate) >= 3  and Day(trun.lastRunDate) < 4, 1 , null)) as "3",
    count(if(Day(trun.lastRunDate) >= 4  and Day(trun.lastRunDate) < 5, 1 , null)) as "4",
    count(if(Day(trun.lastRunDate) >= 5  and Day(trun.lastRunDate) < 6, 1 , null)) as "5",
    count(if(Day(trun.lastRunDate) >= 6  and Day(trun.lastRunDate) < 7, 1 , null)) as "6",
    count(if(Day(trun.lastRunDate) >= 7  and Day(trun.lastRunDate) < 8, 1 , null)) as "7",
    count(if(Day(trun.lastRunDate) >= 8  and Day(trun.lastRunDate) < 9, 1 , null)) as "8",
    count(if(Day(trun.lastRunDate) >= 9  and Day(trun.lastRunDate) < 10, 1 , null)) as "9",
    count(if(Day(trun.lastRunDate) >= 10 and Day(trun.lastRunDate) < 11, 1 , null)) as "10",
    count(if(Day(trun.lastRunDate) >= 11 and Day(trun.lastRunDate) < 12, 1 , null)) as "11",
    count(if(Day(trun.lastRunDate) >= 12 and Day(trun.lastRunDate) < 13, 1 , null)) as "12",
    count(if(Day(trun.lastRunDate) >= 13 and Day(trun.lastRunDate) < 14, 1 , null)) as "13",
    count(if(Day(trun.lastRunDate) >= 14 and Day(trun.lastRunDate) < 15, 1 , null)) as "14",
    count(if(Day(trun.lastRunDate) >= 15 and Day(trun.lastRunDate) < 16, 1 , null)) as "15",
    count(if(Day(trun.lastRunDate) >= 16 and Day(trun.lastRunDate) < 17, 1 , null)) as "16",
    count(if(Day(trun.lastRunDate) >= 17 and Day(trun.lastRunDate) < 18, 1 , null)) as "17",
    count(if(Day(trun.lastRunDate) >= 18 and Day(trun.lastRunDate) < 19, 1 , null)) as "18",
    count(if(Day(trun.lastRunDate) >= 19 and Day(trun.lastRunDate) < 20, 1 , null)) as "19",
    count(if(Day(trun.lastRunDate) >= 20 and Day(trun.lastRunDate) < 21, 1 , null)) as "20",
    count(if(Day(trun.lastRunDate) >= 21 and Day(trun.lastRunDate) < 22, 1 , null)) as "21",
    count(if(Day(trun.lastRunDate) >= 22 and Day(trun.lastRunDate) < 23, 1 , null)) as "22",
    count(if(Day(trun.lastRunDate) >= 23 and Day(trun.lastRunDate) < 24, 1 , null)) as "23",
    count(if(Day(trun.lastRunDate) >= 24 and Day(trun.lastRunDate) < 25, 1 , null)) as "24",
    count(if(Day(trun.lastRunDate) >= 25 and Day(trun.lastRunDate) < 26, 1 , null)) as "25",
    count(if(Day(trun.lastRunDate) >= 26 and Day(trun.lastRunDate) < 27, 1 , null)) as "26",
    count(if(Day(trun.lastRunDate) >= 27 and Day(trun.lastRunDate) < 28, 1 , null)) as "27",
    count(if(Day(trun.lastRunDate) >= 28 and Day(trun.lastRunDate) < 29, 1 , null)) as "28",
    count(if(Day(trun.lastRunDate) >= 29 and Day(trun.lastRunDate) < 30, 1 , null)) as "29",
    count(if(Day(trun.lastRunDate) >= 30 and Day(trun.lastRunDate) < 31, 1 , null)) as "30",
    count(if(Day(trun.lastRunDate) = 31, 1 , null)) as "31",
    count(1) as total

from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
LEFT JOIN
    zt_testtask as tsk 
on 
    tsk.id=trun.task 
LEFT JOIN
    zt_project as pj 
ON
    pj.id=tsk.project
LEFT JOIN
    zt_project as pject 
ON
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
# and
#     pject.status != 'closed'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
group by
    trun.lastRunner
order by
        total
    desc
"""


def construct_sql_case_execute_data_distribute_by_time_slot(
        _date_execute_start,
        _date_execute_end
):
    return f"""
(
    select
        "【总数】" as executor,
        count(if(TIME(trun.lastRunDate) >='8:00'  and TIME(trun.lastRunDate) <  '9:00', 1, null)) as '+8',
        count(if(TIME(trun.lastRunDate) >='9 :00' and TIME(trun.lastRunDate) < '10:00', 1, null)) as '+9',
        count(if(TIME(trun.lastRunDate) >='10:00' and TIME(trun.lastRunDate) < '11:00', 1, null)) as '+10',
        count(if(TIME(trun.lastRunDate) >='11:00' and TIME(trun.lastRunDate) < '12:00', 1, null)) as '+11',
        count(if(TIME(trun.lastRunDate) >='12:00' and TIME(trun.lastRunDate) < '13:30', 1, null)) as '+12',
        count(if(TIME(trun.lastRunDate) >='13:30' and TIME(trun.lastRunDate) < '14:30', 1, null)) as '+13',
        count(if(TIME(trun.lastRunDate) >='14:30' and TIME(trun.lastRunDate) < '15:30', 1, null)) as '+14',
        count(if(TIME(trun.lastRunDate) >='15:30' and TIME(trun.lastRunDate) < '16:30', 1, null)) as '+15',
        count(if(TIME(trun.lastRunDate) >='16:30' and TIME(trun.lastRunDate) < '17:30', 1, null)) as '+16',
        count(if(TIME(trun.lastRunDate) >='17:30' and TIME(trun.lastRunDate) < '18:30', 1, null)) as '+17',
        count(if(TIME(trun.lastRunDate) >='18:30' and TIME(trun.lastRunDate) < '19:30', 1, null)) as '+18',
        count(if(TIME(trun.lastRunDate) >='19:30' and TIME(trun.lastRunDate) < '20:30', 1, null)) as '+19',
        count(if(TIME(trun.lastRunDate) >='20:30' and TIME(trun.lastRunDate) < '21:30', 1, null)) as '+20',
        count(if(TIME(trun.lastRunDate) >='21:30' and TIME(trun.lastRunDate) < '22:30', 1, null)) as '+21',
        count(if(TIME(trun.lastRunDate) >='22:30' and TIME(trun.lastRunDate) <  '8:00', 1, null)) as '+22',
        count(1) as total
    from
        zt_testresult as trest
    left join
        zt_testrun as trun
    on
        trun.id = trest.run
    left join
        zt_case as cas
    on
        cas.id=trun.`case`
    LEFT JOIN
        zt_testtask as tsk 
    on 
        tsk.id=trun.task 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=tsk.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    left join
        zt_user as usr
    on
        usr.account = trun.lastRunner
    where
        trest.run > 0
    and
        cas.deleted = '0'
    and 
        pject.status != 'closed'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
)

UNION

(
    select
        usr.realname as executor,
        count(if(TIME(trun.lastRunDate) >='8:00'  and TIME(trun.lastRunDate) <  '9:00', 1, null)) as '+8',
        count(if(TIME(trun.lastRunDate) >='9 :00' and TIME(trun.lastRunDate) < '10:00', 1, null)) as '+9',
        count(if(TIME(trun.lastRunDate) >='10:00' and TIME(trun.lastRunDate) < '11:00', 1, null)) as '+10',
        count(if(TIME(trun.lastRunDate) >='11:00' and TIME(trun.lastRunDate) < '12:00', 1, null)) as '+11',
        count(if(TIME(trun.lastRunDate) >='12:00' and TIME(trun.lastRunDate) < '13:30', 1, null)) as '+12',
        count(if(TIME(trun.lastRunDate) >='13:30' and TIME(trun.lastRunDate) < '14:30', 1, null)) as '+13',
        count(if(TIME(trun.lastRunDate) >='14:30' and TIME(trun.lastRunDate) < '15:30', 1, null)) as '+14',
        count(if(TIME(trun.lastRunDate) >='15:30' and TIME(trun.lastRunDate) < '16:30', 1, null)) as '+15',
        count(if(TIME(trun.lastRunDate) >='16:30' and TIME(trun.lastRunDate) < '17:30', 1, null)) as '+16',
        count(if(TIME(trun.lastRunDate) >='17:30' and TIME(trun.lastRunDate) < '18:30', 1, null)) as '+17',
        count(if(TIME(trun.lastRunDate) >='18:30' and TIME(trun.lastRunDate) < '19:30', 1, null)) as '+18',
        count(if(TIME(trun.lastRunDate) >='19:30' and TIME(trun.lastRunDate) < '20:30', 1, null)) as '+19',
        count(if(TIME(trun.lastRunDate) >='20:30' and TIME(trun.lastRunDate) < '21:30', 1, null)) as '+20',
        count(if(TIME(trun.lastRunDate) >='21:30' and TIME(trun.lastRunDate) < '22:30', 1, null)) as '+21',
        count(if(TIME(trun.lastRunDate) >='22:30' and TIME(trun.lastRunDate) <  '8:00', 1, null)) as '+22',
        count(1) as total
    from
        zt_testresult as trest
    left join
        zt_testrun as trun
    on
        trun.id = trest.run
    left join
        zt_case as cas
    on
        cas.id=trun.`case`
    LEFT JOIN
        zt_testtask as tsk 
    on 
        tsk.id=trun.task 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=tsk.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    left join
        zt_user as usr
    on
        usr.account = trun.lastRunner
    where
        trest.run > 0
    and
        cas.deleted = '0'
    and 
        pject.status != 'closed'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    group by
        trun.lastRunner
    order by
        total
    desc
)
"""


def construct_sql_case_execute_data_distribute_by_time_slot_with_all(
        _date_execute_start,
        _date_execute_end,
        _executor_name,
        _case_type
):
    return f"""
    select
        "【总数】" as executor,
        count(if(TIME(trun.lastRunDate) >='8:00'  and TIME(trun.lastRunDate) <  '9:00', 1, null)) as '+8',
        count(if(TIME(trun.lastRunDate) >='9 :00' and TIME(trun.lastRunDate) < '10:00', 1, null)) as '+9',
        count(if(TIME(trun.lastRunDate) >='10:00' and TIME(trun.lastRunDate) < '11:00', 1, null)) as '+10',
        count(if(TIME(trun.lastRunDate) >='11:00' and TIME(trun.lastRunDate) < '12:00', 1, null)) as '+11',
        count(if(TIME(trun.lastRunDate) >='12:00' and TIME(trun.lastRunDate) < '13:30', 1, null)) as '+12',
        count(if(TIME(trun.lastRunDate) >='13:30' and TIME(trun.lastRunDate) < '14:30', 1, null)) as '+13',
        count(if(TIME(trun.lastRunDate) >='14:30' and TIME(trun.lastRunDate) < '15:30', 1, null)) as '+14',
        count(if(TIME(trun.lastRunDate) >='15:30' and TIME(trun.lastRunDate) < '16:30', 1, null)) as '+15',
        count(if(TIME(trun.lastRunDate) >='16:30' and TIME(trun.lastRunDate) < '17:30', 1, null)) as '+16',
        count(if(TIME(trun.lastRunDate) >='17:30' and TIME(trun.lastRunDate) < '18:30', 1, null)) as '+17',
        count(if(TIME(trun.lastRunDate) >='18:30' and TIME(trun.lastRunDate) < '19:30', 1, null)) as '+18',
        count(if(TIME(trun.lastRunDate) >='19:30' and TIME(trun.lastRunDate) < '20:30', 1, null)) as '+19',
        count(if(TIME(trun.lastRunDate) >='20:30' and TIME(trun.lastRunDate) < '21:30', 1, null)) as '+20',
        count(if(TIME(trun.lastRunDate) >='21:30' and TIME(trun.lastRunDate) < '22:30', 1, null)) as '+21',
        count(if(TIME(trun.lastRunDate) >='22:30' and TIME(trun.lastRunDate) <  '8:00', 1, null)) as '+22',
        count(1) as total
    from
        zt_testresult as trest
    left join
        zt_testrun as trun
    on
        trun.id = trest.run
    left join
        zt_case as cas
    on
        cas.id=trun.`case`
    LEFT JOIN
        zt_testtask as tsk 
    on 
        tsk.id=trun.task 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=tsk.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    left join
        zt_user as usr
    on
        usr.account = trun.lastRunner
    where
        trest.run > 0
    and
        cas.deleted = '0'
    and 
        pject.status != 'closed'    
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
"""


def construct_sql_case_execute_data_distribute_by_time_slot_with_each(
        _date_execute_start,
        _date_execute_end,
        _executor_name,
        _case_type
):
    return f"""
    select
        usr.realname as executor,
        count(if(TIME(trun.lastRunDate) >='8:00'  and TIME(trun.lastRunDate) <  '9:00', 1, null)) as '+8',
        count(if(TIME(trun.lastRunDate) >='9 :00' and TIME(trun.lastRunDate) < '10:00', 1, null)) as '+9',
        count(if(TIME(trun.lastRunDate) >='10:00' and TIME(trun.lastRunDate) < '11:00', 1, null)) as '+10',
        count(if(TIME(trun.lastRunDate) >='11:00' and TIME(trun.lastRunDate) < '12:00', 1, null)) as '+11',
        count(if(TIME(trun.lastRunDate) >='12:00' and TIME(trun.lastRunDate) < '13:30', 1, null)) as '+12',
        count(if(TIME(trun.lastRunDate) >='13:30' and TIME(trun.lastRunDate) < '14:30', 1, null)) as '+13',
        count(if(TIME(trun.lastRunDate) >='14:30' and TIME(trun.lastRunDate) < '15:30', 1, null)) as '+14',
        count(if(TIME(trun.lastRunDate) >='15:30' and TIME(trun.lastRunDate) < '16:30', 1, null)) as '+15',
        count(if(TIME(trun.lastRunDate) >='16:30' and TIME(trun.lastRunDate) < '17:30', 1, null)) as '+16',
        count(if(TIME(trun.lastRunDate) >='17:30' and TIME(trun.lastRunDate) < '18:30', 1, null)) as '+17',
        count(if(TIME(trun.lastRunDate) >='18:30' and TIME(trun.lastRunDate) < '19:30', 1, null)) as '+18',
        count(if(TIME(trun.lastRunDate) >='19:30' and TIME(trun.lastRunDate) < '20:30', 1, null)) as '+19',
        count(if(TIME(trun.lastRunDate) >='20:30' and TIME(trun.lastRunDate) < '21:30', 1, null)) as '+20',
        count(if(TIME(trun.lastRunDate) >='21:30' and TIME(trun.lastRunDate) < '22:30', 1, null)) as '+21',
        count(if(TIME(trun.lastRunDate) >='22:30' and TIME(trun.lastRunDate) <  '8:00', 1, null)) as '+22',
        count(1) as total
    from
        zt_testresult as trest
    left join
        zt_testrun as trun
    on
        trun.id = trest.run
    left join
        zt_case as cas
    on
        cas.id=trun.`case`
    LEFT JOIN
        zt_testtask as tsk 
    on 
        tsk.id=trun.task 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=tsk.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    left join
        zt_user as usr
    on
        usr.account = trun.lastRunner
    where
        trest.run > 0
    and
        cas.deleted = '0'
    and 
        pject.status != 'closed'   
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
    group by
        trun.lastRunner
    order by
        total
    desc
"""


def construct_sql_case_create_data_distribute_by_date_with_all_old(
    _date_create_start,
    _date_create_end
):
    return f"""
(
    select
            "【总数】"                                  as creator,
            count(if(Day(cas.openedDate) >= 1  and Day(cas.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(cas.openedDate) >= 2  and Day(cas.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(cas.openedDate) >= 3  and Day(cas.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(cas.openedDate) >= 4  and Day(cas.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(cas.openedDate) >= 5  and Day(cas.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(cas.openedDate) >= 6  and Day(cas.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(cas.openedDate) >= 7  and Day(cas.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(cas.openedDate) >= 8  and Day(cas.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(cas.openedDate) >= 9  and Day(cas.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(cas.openedDate) >= 10 and Day(cas.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(cas.openedDate) >= 11 and Day(cas.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(cas.openedDate) >= 12 and Day(cas.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(cas.openedDate) >= 13 and Day(cas.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(cas.openedDate) >= 14 and Day(cas.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(cas.openedDate) >= 15 and Day(cas.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(cas.openedDate) >= 16 and Day(cas.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(cas.openedDate) >= 17 and Day(cas.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(cas.openedDate) >= 18 and Day(cas.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(cas.openedDate) >= 19 and Day(cas.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(cas.openedDate) >= 20 and Day(cas.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(cas.openedDate) >= 21 and Day(cas.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(cas.openedDate) >= 22 and Day(cas.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(cas.openedDate) >= 23 and Day(cas.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(cas.openedDate) >= 24 and Day(cas.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(cas.openedDate) >= 25 and Day(cas.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(cas.openedDate) >= 26 and Day(cas.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(cas.openedDate) >= 27 and Day(cas.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(cas.openedDate) >= 28 and Day(cas.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(cas.openedDate) >= 29 and Day(cas.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(cas.openedDate) >= 30 and Day(cas.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(cas.openedDate) = 31, 1 , null)) as "31", 
            count(cas.id)                                   as total
     from zt_case as cas
              left join
          zt_user as usr
          on
              usr.account = cas.openedBy
     where cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
 )

union
(
    select
            usr.realname                                    as creator,
            count(if(Day(cas.openedDate) >= 1  and Day(cas.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(cas.openedDate) >= 2  and Day(cas.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(cas.openedDate) >= 3  and Day(cas.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(cas.openedDate) >= 4  and Day(cas.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(cas.openedDate) >= 5  and Day(cas.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(cas.openedDate) >= 6  and Day(cas.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(cas.openedDate) >= 7  and Day(cas.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(cas.openedDate) >= 8  and Day(cas.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(cas.openedDate) >= 9  and Day(cas.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(cas.openedDate) >= 10 and Day(cas.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(cas.openedDate) >= 11 and Day(cas.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(cas.openedDate) >= 12 and Day(cas.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(cas.openedDate) >= 13 and Day(cas.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(cas.openedDate) >= 14 and Day(cas.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(cas.openedDate) >= 15 and Day(cas.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(cas.openedDate) >= 16 and Day(cas.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(cas.openedDate) >= 17 and Day(cas.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(cas.openedDate) >= 18 and Day(cas.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(cas.openedDate) >= 19 and Day(cas.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(cas.openedDate) >= 20 and Day(cas.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(cas.openedDate) >= 21 and Day(cas.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(cas.openedDate) >= 22 and Day(cas.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(cas.openedDate) >= 23 and Day(cas.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(cas.openedDate) >= 24 and Day(cas.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(cas.openedDate) >= 25 and Day(cas.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(cas.openedDate) >= 26 and Day(cas.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(cas.openedDate) >= 27 and Day(cas.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(cas.openedDate) >= 28 and Day(cas.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(cas.openedDate) >= 29 and Day(cas.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(cas.openedDate) >= 30 and Day(cas.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(cas.openedDate) = 31, 1 , null)) as "31", 
            count(cas.id)                                   as total
    from 
        zt_case as cas
    left join
        zt_user as usr
    on
        usr.account = cas.openedBy
     where 
        cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
     group by 
        cas.openedBy
    order by
        total
    desc
 )    
"""


def construct_sql_case_create_data_distribute_by_date_with_all(
    _date_create_start,
    _date_create_end,
    _creator_name
):
    return f"""
    select
            "【总数】"                                  as creator,
            count(if(Day(cas.openedDate) >= 1  and Day(cas.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(cas.openedDate) >= 2  and Day(cas.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(cas.openedDate) >= 3  and Day(cas.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(cas.openedDate) >= 4  and Day(cas.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(cas.openedDate) >= 5  and Day(cas.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(cas.openedDate) >= 6  and Day(cas.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(cas.openedDate) >= 7  and Day(cas.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(cas.openedDate) >= 8  and Day(cas.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(cas.openedDate) >= 9  and Day(cas.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(cas.openedDate) >= 10 and Day(cas.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(cas.openedDate) >= 11 and Day(cas.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(cas.openedDate) >= 12 and Day(cas.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(cas.openedDate) >= 13 and Day(cas.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(cas.openedDate) >= 14 and Day(cas.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(cas.openedDate) >= 15 and Day(cas.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(cas.openedDate) >= 16 and Day(cas.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(cas.openedDate) >= 17 and Day(cas.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(cas.openedDate) >= 18 and Day(cas.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(cas.openedDate) >= 19 and Day(cas.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(cas.openedDate) >= 20 and Day(cas.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(cas.openedDate) >= 21 and Day(cas.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(cas.openedDate) >= 22 and Day(cas.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(cas.openedDate) >= 23 and Day(cas.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(cas.openedDate) >= 24 and Day(cas.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(cas.openedDate) >= 25 and Day(cas.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(cas.openedDate) >= 26 and Day(cas.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(cas.openedDate) >= 27 and Day(cas.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(cas.openedDate) >= 28 and Day(cas.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(cas.openedDate) >= 29 and Day(cas.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(cas.openedDate) >= 30 and Day(cas.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(cas.openedDate) = 31, 1 , null)) as "31", 
            count(cas.id)                                   as total
    from 
        zt_case as cas
    left join
        zt_user as usr
    on
        usr.account = cas.openedBy
     where 
        cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND  
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
    {
    '''AND
            usr.realname = '%s' # case创建人''' % _creator_name if _creator_name else ''
    }    
"""


def construct_sql_case_create_data_distribute_by_date_with_each(
    _date_create_start,
    _date_create_end,
    _creator_name
):
    return f"""
    select
            usr.realname                                    as creator,
            count(if(Day(cas.openedDate) >= 1  and Day(cas.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(cas.openedDate) >= 2  and Day(cas.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(cas.openedDate) >= 3  and Day(cas.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(cas.openedDate) >= 4  and Day(cas.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(cas.openedDate) >= 5  and Day(cas.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(cas.openedDate) >= 6  and Day(cas.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(cas.openedDate) >= 7  and Day(cas.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(cas.openedDate) >= 8  and Day(cas.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(cas.openedDate) >= 9  and Day(cas.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(cas.openedDate) >= 10 and Day(cas.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(cas.openedDate) >= 11 and Day(cas.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(cas.openedDate) >= 12 and Day(cas.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(cas.openedDate) >= 13 and Day(cas.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(cas.openedDate) >= 14 and Day(cas.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(cas.openedDate) >= 15 and Day(cas.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(cas.openedDate) >= 16 and Day(cas.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(cas.openedDate) >= 17 and Day(cas.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(cas.openedDate) >= 18 and Day(cas.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(cas.openedDate) >= 19 and Day(cas.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(cas.openedDate) >= 20 and Day(cas.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(cas.openedDate) >= 21 and Day(cas.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(cas.openedDate) >= 22 and Day(cas.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(cas.openedDate) >= 23 and Day(cas.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(cas.openedDate) >= 24 and Day(cas.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(cas.openedDate) >= 25 and Day(cas.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(cas.openedDate) >= 26 and Day(cas.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(cas.openedDate) >= 27 and Day(cas.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(cas.openedDate) >= 28 and Day(cas.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(cas.openedDate) >= 29 and Day(cas.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(cas.openedDate) >= 30 and Day(cas.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(cas.openedDate) = 31, 1 , null)) as "31", 
            count(cas.id)                                   as total
    from 
        zt_case as cas
    left join
          zt_user as usr
    on
              usr.account = cas.openedBy
     where cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
    {
    '''AND
            usr.realname = '%s' # Bug关闭人''' % _creator_name if _creator_name else ''
    }    
     group by 
        cas.openedBy
    order by
        total
    desc
"""


def construct_sql_case_create_data_distribute_by_week(
    _date_create_start,
    _date_create_end
):
    return f"""
(
    select
            " "                                  as creator,
            "【总数】"                                        as week_day,
            count(IF(WEEKDAY(cas.openedDate) = 0, 1, null)) as Monday,
            count(IF(WEEKDAY(cas.openedDate) = 1, 1, null)) as Tuesday,
            count(IF(WEEKDAY(cas.openedDate) = 2, 1, null)) as Wednesday,
            count(IF(WEEKDAY(cas.openedDate) = 3, 1, null)) as Thursday,
            count(IF(WEEKDAY(cas.openedDate) = 4, 1, null)) as Friday,
            count(IF(WEEKDAY(cas.openedDate) = 5, 1, null)) as Saturday,
            count(IF(WEEKDAY(cas.openedDate) = 6, 1, null)) as Sunday,
            count(cas.id)                                   as total
     from zt_case as cas
              left join
          zt_user as usr
          on
              usr.account = cas.openedBy
     where cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
 )

union
(
    select
            usr.realname                                    as creator,
            WEEKDAY(cas.openedDate) + 1                     as week_day,
            count(IF(WEEKDAY(cas.openedDate) = 0, 1, null)) as Monday,
            count(IF(WEEKDAY(cas.openedDate) = 1, 1, null)) as Tuesday,
            count(IF(WEEKDAY(cas.openedDate) = 2, 1, null)) as Wednesday,
            count(IF(WEEKDAY(cas.openedDate) = 3, 1, null)) as Thursday,
            count(IF(WEEKDAY(cas.openedDate) = 4, 1, null)) as Friday,
            count(IF(WEEKDAY(cas.openedDate) = 5, 1, null)) as Saturday,
            count(IF(WEEKDAY(cas.openedDate) = 6, 1, null)) as Sunday,
            count(cas.id)                                   as total
     from zt_case as cas
              left join
          zt_user as usr
          on
              usr.account = cas.openedBy
     where cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
     group by 
        cas.openedBy
    order by
        total
    desc
 )    
"""










































